//======================
// 路由控制器
//======================

// 导入API, 操作mysql数据库
let api = require(__basename + '/api/api.js');

// 导入utils, 调用公共方法
let utils = require(__basename + '/utils/utils.js');

let { Op } = require("sequelize");

class RoutesController {
  // 测试接口
  test(req, res) {
    res.send({
      msg: 'test success'
    })
  }
  // 请求域拦截
  verfiyHost(req, res, next) {
    if (config.hostOptions.indexOf(req.headers.origin) === -1) {
      return res.send({
        msg: '请求域不合法',
        code: 1020
      });
    }
    //允许通过
    next();
  }

  // 验证码拦截
  verifyCode(req, res, next) {
    let url = req.url.split('?')[0];
    if (config.codeUrlOptions.indexOf(url) > -1) {
      //根据codeId查询验证码信息
      api.findData({
        modelName: 'Code',
        where: {
          codeId: req.body.codeId
        }
      }).then(result => {
        console.log('result ==> ', result);
        //获取当前时间 和 验证码有效时间差
        let time = new Date().getTime() - config.emailOptions.expires;
        //获取验证码保存时间
        let codeTime = new Date(result[0].dataValues.createdAt).getTime()
        //如果验证码保存时间 >= time
        let isPass = req.body.validcode == result[0].dataValues.text && req.body.email == result[0].dataValues.email && codeTime >= time;
        if (isPass) {
          //如果验证通过，则将请求传递给下一个中间件或者路由
          next();
        } else {
          res.send({
            msg: '验证码错误或失效',
            code: 1031
          });
        }
      }).catch(err => {
        console.log('err ==> ', err);
        res.send({
          msg: '请重新获取验证码',
          code: 1032
        });
      })
    } else {
      //无需验证验证码, 直接将请求传递给下一个中间或者路由
      next();
    }
  }

  // 验证Token(验证登录)
  verifyToken(req, res, next) {
    console.log('验证token')
    let url = req.url.split('?')[0];
    // console.log('headers ==>', req.headers)
    if (config.tokenOptions.tokenUrls.indexOf(url) > -1) {
      // 需要验证token
      console.log('cookie ==>', req.headers.cookie)
      if (!req.headers.cookie) {
        return res.send({ msg: '请先登录', code: 1034 });
      }

      let cookie = utils.transformCookie(req.headers.cookie);

      // let token = [cookie.mama12, cookie.nana20, cookie.mama20].join('.');
      let token = cookie.__tk;
      console.log('token ==> ', token);

      // 验证token
      utils.verifyToken(token).then(result => {
        console.log('result ==> ', result);
        // 将id传递
        req.id = result.data;
        console.log('result.data==>', result.data)
        // 验证通过，传递给下一个中间或者路由
        next();

      }).catch(err => {
        console.log(err)
        res.send({ msg: '请先登录', code: 1034 });
      })


    } else {
      next();
    }

  }

  // 后台注册接口
  b_register(req, res) {
    //查询邮箱是否已经被注册
    api.findData({
      modelName: 'Admin',
      where: {
        email: req.body.email
      },
      attributes: ['email']
    }).then(result => {
      //如果邮箱已经被注册，则提示用户该邮箱已经被注册
      if (result.length > 0) {
        res.send({
          msg: '该邮箱已经被注册',
          code: 1002
        });
      } else {
        //注册
        //创建用户id
        let adminId = '_aid' + new Date().getTime();

        //加密密码
        // let password = utils.encodeString(req.body.password);
        // console.log('password ==> ', password);

        //添加用户数据，注册用户
        api.createData('Admin', {
          email: req.body.email,
          adminName: req.body.adminName,
          password: req.body.password,
          adminId
        }).then(result => {
          res.send({
            msg: '注册成功',
            code: 1000,
            result
          });
        }).catch(err => {
          console.log('err ==> ', err);
          res.send({
            msg: '注册失败',
            code: 1001
          });
        })

      }

    }).catch(err => {
      console.log('err ==> ', err);
      res.send({
        msg: '注册失败',
        code: 1001
      });
    })


  }

  // 后台登录接口
  b_login(req, res) {
    api.findData({
      modelName: 'Admin',
      where: {
        email: req.body.email,
      },
      attributes: ['adminId', 'password', 'position']
    }).then(result => {
      console.log('reslut==>', result)
      // 如果存在用户
      if (result.length > 0) {
        // 验证密码是否正确
        // let password = utils.encodeString(req.body.password);// 密码加密
        if (req.body.password == result[0].dataValues.password) {
          // 生成token：加密的字符串，一般用于身份验证，登录验证
          let token = utils.signToken(result[0].dataValues.adminId);
          console.log('token ==> ', token);

          // 将token切片
          // let ts = token.split('.');

          // let tsObj = {
          //   mama12: ts[0],
          //   nana20: ts[1],
          //   mama20: ts[2],
          //   // 干扰项
          //   nana19: 'uyTrgabciOGHgsadrtjhaCI6Ik98EwahbvD'
          // }

          res.send({
            msg: '登录成功',
            code: 1030,
            // data: tsObj
            data: { token: token, aid: result[0].dataValues.adminId, position: result[0].dataValues.position }
          });
        } else {
          // 密码错误
          res.send({
            msg: '密码不正确',
            code: 1033
          })
        }
      } else {
        // 用户不存在
        res.send({
          msg: '用户不存在',
          code: 1032
        })
      }
    }).catch(err => {
      console.log('err ==> ', err);
      res.send({
        msg: '登录失败',
        code: 1031
      })
    })
  }

  // 邮箱验证码接口
  email(req, res) {
    // console.log('req.body ==> ', req.body);
    // 随机生成验证码
    let code = utils.randomCode();
    console.log('code==>', code)
    // 生成唯一的验证码id
    let codeId = '_cid' + new Date().getTime();
    // 先存储验证码, 再发邮件给用户
    api.createData('Code', {
      email: req.body.email,
      codeId,
      text: code
    }).then(result => {
      // 创建成功
      if (result.dataValues) {
        //开发时，屏蔽发邮件
        res.send({
          msg: `验证码已发至${req.body.email}`,
          code: 1010,
          cid: codeId,
          Code: code
        });
        // return;

        //发邮件
        utils.sendEmail({
          to: req.body.email,
          subject: '验证码',
          text: `验证码为：${code},5分钟内有效。`
        }).then(result => {
          res.send({
            msg: `验证码已发至${result.accepted[0]}`,
            code: 1010,
            cid: codeId
          });
        }).catch(err => {
          console.log('err ==> ', err);
          res.send({
            msg: '发送验证码失败',
            code: 1011
          });
        })
      }
    }).catch(err => {
      console.log(err)
      res.send({
        msg: '发送验证码失败',
        code: 1011
      });
    })
  }

  // 登录接口
  login(req, res) {
    console.log('req.body==> ', req.body)
    api.findData({
      modelName: 'User',
      where: {
        // email: req.body.email,
        phone: req.body.phone
      },
      attributes: ['userId', 'password', 'userName']
    }).then(result => {
      console.log('reslut==>', result)
      // 如果存在用户
      if (result.length > 0) {
        // 验证密码是否正确
        // let password = utils.encodeString(req.body.password);//密码加密
        if (req.body.password == result[0].dataValues.password) {
          // console.log('result[0].dataValues==>', result[0].dataValues)
          //生成token：加密的字符串，一般用于身份验证，登录验证
          let token = utils.signToken(result[0].dataValues.userId);
          console.log('token ==> ', token);

          // 将token切片
          // let ts = token.split('.');

          // let tsObj = {
          //   mama12: ts[0],
          //   nana20: ts[1],
          //   mama20: ts[2],
          //   //干扰项
          //   nana19: 'uyTrgabciOGHgsadrtjhaCI6Ik98EwahbvD'
          // }

          res.send({
            msg: '登录成功',
            code: 1030,
            // data: tsObj
            data: {
              token: token,
              userId: result[0].dataValues.userId,
              userName: result[0].dataValues.userName
            }
          });
        } else {
          // 密码错误
          res.send({
            msg: '密码不正确',
            code: 1033
          })
        }
      } else {
        // 用户不存在
        res.send({
          msg: '用户不存在',
          code: 1032
        })
      }
    }).catch(err => {
      console.log('err ==> ', err);
      res.send({
        msg: '登录失败',
        code: 1031
      })
    })
  }

  // 前端注册接口
  register(req, res) {
    //查询邮箱是否已经被注册
    api.findData({
      modelName: 'User',
      where: {
        email: req.body.email
      },
      attributes: ['email']
    }).then(result => {
      //如果邮箱已经被注册，则提示用户该邮箱已经被注册
      if (result.length > 0) {
        res.send({
          msg: '该邮箱已经被注册',
          code: 1002
        });
      } else {
        api.findData({
          modelName: 'User',
          where: {
            phone: req.body.phone
          },
          attributes: ['phone']
        }).then(result => {
          if (result.length > 0) {
            res.send({
              msg: '该手机号已经被注册',
              code: 1002
            });
          } else {
            //创建用户id
            let userId = '_uid' + new Date().getTime();

            //加密密码
            // let password = utils.encodeString(req.body.password);
            // console.log('password ==> ', password);

            //添加用户数据，注册用户
            api.createData('User', {
              email: req.body.email,
              userName: req.body.userName,
              password: req.body.password,
              phone: req.body.phone,
              userId
            }).then(result => {
              res.send({
                msg: '注册成功',
                code: 1000,
                result
              });
            }).catch(err => {
              console.log('err ==> ', err);
              res.send({
                msg: '注册失败',
                code: 1001
              });
            })
          }
        }).catch(err => {
          console.log('err ==> ', err);
          res.send({
            msg: '注册失败',
            code: 1001
          });
        })

      }

    }).catch(err => {
      console.log('err ==> ', err);
      res.send({
        msg: '注册失败',
        code: 1001
      });
    })

  }

  // 获取用户信息
  getUserInfo(req, res) {
    // console.log('req ==> ', req);
    console.log('userId ==> ', req.query.userId);
    api.findData({
      modelName: 'User',
      where: {
        userId: req.query.userId
      }
    }).then(result => {
      let url = config.serverOptions.host;
      if (config.serverOptions.port) {
        url += `:${config.serverOptions.port}`
      }

      url += config.serverOptions.baseUrl

      res.send({
        msg: '查询用户信息成功',
        code: 1040,
        data: {
          result,
          url
        }
      })
    }).catch(err => {
      res.send({
        msg: '查询用户信息失败',
        code: 1041
      });
    })
  }

  // 获取管理员信息
  getAdminInfo(req, res) {
    console.log('req.adminId ==> ', req.id);
    api.findData({
      modelName: 'Admin',
      where: {
        adminId: req.id
      }
    }).then(result => {
      console.log('result==>', result)
      if (result.length != 0) {

        let url = config.serverOptions.host;
        if (config.serverOptions.port) {
          url += `:${config.serverOptions.port}`;
        }

        url += config.serverOptions.baseUrl;

        res.send({
          msg: '查询管理员信息成功',
          code: 1040,
          data: {
            result,
            url
          }
        })
      } else {
        res.send({
          msg: '查询管理员信息失败',
          code: 1041
        });
      }

    }).catch(err => {
      res.send({
        msg: '查询管理员信息失败',
        code: 1041
      });
    })
  }

  // 编辑用户信息/修改密码
  editUser(req, res) {
    console.log('-----------------------------------------------')
    console.log('req.body==>', req.body)
    // console.log('userId ==> ', req.query.userId);
    let userId = req.id;
    if (req.body.o_pw) {
      console.log('req.body.o_pw==>', req.body.o_pw)
      api.findData({
        modelName: 'User',
        where: {
          userId: req.id
        },
        attributes: ['password']
      }).then(result => {
        console.log('result==>', result)
        if (req.body.o_pw == result[0].dataValues.password) {
          api.updateData(
            'User',
            { password: req.body.password },
            { userId }
          ).then(result => {
            console.log('result==>', result);
            res.send({ msg: '修改密码成功', code: 200 });
          }).catch(err => {
            console.log('err==>', err)
            res.send({ msg: '修改密码失败', code: 400 })
          })
        } else {
          res.send({ msg: '旧密码不正确', code: 400 })
        }
      }).catch(err => {
        console.log('err==>', err)
      })
    } else {
      api.updateData(
        'User',
        Object.assign(req.body),
        { userId }).then(result => {
          console.log('result==>', result);
          res.send({ msg: '更新信息成功', code: 200 });
        }).catch(err => {
          console.log('err==>', err)
          res.send({ msg: '更新信息失败', code: 400 })
        })
    }
  }
  // 验证用户邮箱
  checkEmail(req, res) {
    // console.log('-----------------------------验证用户邮箱---------------------------------')
    // console.log('req.body==>', req.body)
    api.findData({
      modelName: 'User',
      where: {
        email: req.body.email,
      }
    }).then(result => {
      console.log('result==>', result)
      if (result.length == 1) {
        res.send({ data: result, code: 200, msg: '验证成功' })
      } else if (result.length <= 0) {
        res.send({ code: 400, msg: '该邮箱未注册' })
      }
    }).catch(err => {
      console.log('err==>', err)
    })
  }
  // 修改密码
  changePw(req, res) {
    api.updateData(
      'User',
      { password: req.body.password },
      { email: req.body.email }
    ).then(result => {
      console.log('result==>', result);
      res.send({ msg: '修改密码成功', code: 200 });
    }).catch(err => {
      console.log('err==>', err)
      res.send({ msg: '修改密码失败', code: 400 })
    })
  }
  // 获取交易进度
  getProgress(req, res) {
    console.log('--------------------获取交易进度-----------------------')
    // console.log('req.query', req.query)
    // return
    api.findCount2({
      modelName: 'Transaction',
      where: {
        userId: req.query.userId,
        isDel: 0
      },
      limit: 10,
      offset: Number(req.query.page) - 1,
      order: sequelize.literal('createdAt DESC'),
    }).then(result => {
      console.log('result==>', result)
      if (result.length < 0) {
        res.send({ data: result, code: 400, msg: '查询失败' })
      } else {
        res.send({ data: result.rows, code: 200, msg: '查询成功', total: result.count, page: req.query.page })
      }

    }).catch(err => {
      console.log('err==>', err)
      res.send({ data: result, code: 400, msg: '查询失败' })
    })
  }
  // 删除交易进度
  deleteProgress(req, res) {
    console.log('----------------------------删除交易进度---------------------------------')
    api.transaction(async (t) => {
      await api.updateData('Transaction', { isDel: req.body.isDel }, { id: req.body.id }, t);
    }).then(result => {
      // console.log('result==>', result)
      res.send({ msg: '删除成功', code: 200 });
    }).catch(err => {
      console.log('err ==> ', err);
      res.send({ msg: '删除失败', code: 400 });
    })
  }
  // 获取委托列表
  getSellingRecord(req, res) {
    console.log('--------------------获取委托列表-------------------')
    // 查询手机号对应用户id
    api.findData({
      modelName: 'User',
      where: {
        userId: req.query.userId,
      },
      attributes: ['phone']
    }).then(result1 => {
      console.log('result1==>', result1)
      let phone = result1[0].dataValues.phone;
      // 查询委托记录
      api.findCount({
        modelName: 'SecondHandHouse',
        where: {
          userPhone: phone,
        },
        limit: 10,
        offset: Number(req.query.page) - 1,
        order: sequelize.literal('createdAt DESC'),
        include: {
          modelName: 'Estate',
          attributes: ['estateName', 'location', 'city']
        }
      }).then(result => {
        console.log('result==>', result)
        if (result.length < 0) {
          res.send({ data: result, code: 400, msg: '查询失败' })
        } else {
          res.send({ data: result.rows, code: 200, msg: '查询成功', total: result.count, page: req.query.page })
        }

      }).catch(err => {
        console.log('err==>', err)
        res.send({ data: result, code: 400, msg: '查询失败' })
      })

    }).catch(err => {
      console.log('err==>', err)
      res.send({ data: result, code: 400, msg: '查询失败' })
    })

  }
  // 修改房源状态
  houseState(req, res) {
    console.log('-----------------------修改房源状态---------------------------')
    console.log('req.query==>', req.query)
    let data = {
      state: Number(req.query.state)
    }
    if (req.query.state == 3) {
      data.sellingTime = null
    }
    if (req.query.state == 1) {
      data.sellingTime = new Date()
    }
    if (req.query.state == -1) {
      data.sellingTime = null
    }
    if (req.query.state == 2) {
      data.transactionDate = new Date()
    }

    api.transaction(async (t) => {
      await api.updateData(
        'SecondHandHouse',
        data,
        { id: req.query.id },
        t
      );
    }).then(result => {
      // console.log('result==>', result)
      res.send({ msg: '修改状态成功', code: 200 });
    }).catch(err => {
      console.log('err ==> ', err);
      res.send({ msg: '修改状态失败', code: 400 });
    })
  }
  // 获取小区名和id
  getEstate(req, res) {
    console.log('-----------------------获取小区列表---------------------------')
    // console.log('req.query==>', req.query)
    api.findData({
      modelName: 'Estate',
      attributes: ["estateName", "address", "estateId"]
    }).then(result => {
      // console.log('result==>', result)
      if (result.length > 0) {
        res.send({ msg: '查询小区成功', code: 200, data: result });
      } else {
        res.send({ msg: '查询小区失败', code: 400 });
      }
    }).catch(err => {
      console.log('err==>', err)
      res.send({ msg: '查询小区失败', code: 400 });
    })
  }
  // 添加房源
  addHouse(req, res) {
    console.log('-----------------------添加房源---------------------------')
    console.log('req.body==>', req.body)
    // let data = {
    //   estateId: req.body.estateId,
    //   houseNum: req.body.houseNum,
    //   kind: req.body.kind,
    //   sup: Number(req.body.sup),
    //   builtArea:Number( req.body.builtArea),
    //   sub: Number(req.body.sub),
    //   kind1: Number(req.body.kind1),
    //   kind2: Number(req.body.kind2),
    //   kind3: Number(req.body.kind3),
    // };
    // if(!req.body.userName && !req.body.userPhone){
    //   data.userName = req.body.userName;
    //   data.userPhone = req.body.userPhone;
    // }
    if (req.body.img) {
      //先上传图片
      let promise = [
        utils.uploadImg(req.body.img, req.body.imgType),
        // utils.uploadImg(req.body.detailImg, req.body.detailImgType)
      ];

      //等待所有图片都上传完成后，再将房源数据写入mysql数据库
      Promise.all(promise).then(result => {
        //房源数据
        let houseData = Object.assign(req.body);
        houseData.img = result[0];
        // houseData.detailImg = result[1];

        //删除房源类型,图片类型
        // delete houseData.type;
        delete houseData.imgType;
        // delete houseData.detailImgType;

        //启动事务处理
        api.transaction(t => {
          return Promise.all([
            api.createData('SecondHandHouse', houseData, t)
          ])
        }).then(result => {
          res.send({ msg: '发布房源成功', code: 200, result });
        }).catch(err => {
          console.log('err ==> ', err);
          res.send({ msg: '发布房源失败', code: 400 });
        })

      }).catch(err => {
        console.log('err ==> ', err);
        res.send({ msg: '发布房源失败', code: 400 });
      })

    } else {
      api.createData('SecondHandHouse', req.body).then(result => {
        console.log('result==>', result)
        res.send({ code: 200, msg: '委托成功, 请等待审核通过' })
      }).catch(err => {
        console.log('err==>', err)
        res.send({ code: 400, msg: '委托失败, 请重新发布委托' })
      })
    }
  }
  // 编辑房源信息
  editHouse(req, res) {
    console.log('-----------------------编辑房源信息---------------------------')
    console.log('body==>', req.body)
    api.transaction(async (t) => {
      await api.updateData(
        'SecondHandHouse',
        req.body,
        { id: req.body.id },
        t
      );
    }).then(result => {
      // console.log('result==>', result)
      res.send({ msg: '修改成功', code: 200 });
    }).catch(err => {
      console.log('err ==> ', err);
      res.send({ msg: '修改失败', code: 400 });
    })
  }
  // 删除房源记录
  deleteHouse(req, res) {
    console.log('-----------------------删除房源记录---------------------------')
    console.log('req.query==>', req.query)
    api.transaction(async (t) => {
      await api.removeData(
        'SecondHandHouse',
        { id: req.query.id },
        t
      );
    }).then(result => {
      // console.log('result==>', result)
      res.send({ msg: '删除成功', code: 200 });
    }).catch(err => {
      console.log('err ==> ', err);
      res.send({ msg: '删除失败', code: 400 });
    })
  }
  // 获取二手房列表
  getHouseList(req, res) {
    console.log('-----------------二手房列表----------------')
    console.log('query==>', req.query)
    let params = {
      offset: (Number(req.query.page) - 1) * Number(req.query.size),
      limit: Number(req.query.size),
    };

    let sql = "SELECT `secondHandHouse`.`id`, `secondHandHouse`.`title`, `secondHandHouse`.`built_area` AS `builtArea`, `secondHandHouse`.`inside_area` AS `insideArea`, `secondHandHouse`.`floor`, `secondHandHouse`.`sup`, `secondHandHouse`.`sub`, `secondHandHouse`.`kind`, `secondHandHouse`.`decoration`, `secondHandHouse`.`orientation`, `secondHandHouse`.`built_year` AS `builtYear`, `secondHandHouse`.`purpose`, `secondHandHouse`.`nature`, `secondHandHouse`.`property_year` AS `propertyYear`, `secondHandHouse`.`lights_desc`, `secondHandHouse`.`dec_desc`, `secondHandHouse`.`kind_desc`, `secondHandHouse`.`tag1`, `secondHandHouse`.`tag2`, `secondHandHouse`.`tag3`, `secondHandHouse`.`tag4`, `secondHandHouse`.`tag5`, `secondHandHouse`.`traffic`, `secondHandHouse`.`selling_time` AS `sellingTime`, `secondHandHouse`.`transaction_date` AS `transactionDate`, `secondHandHouse`.`img`, `secondHandHouse`.`img_list` AS `imgList`, `secondHandHouse`.`house_num` AS `houseNum`, `secondHandHouse`.`branch`, `secondHandHouse`.`state`, `secondHandHouse`.`estate_id` AS `estateId`, `secondHandHouse`.`user_name` AS `userName`, `secondHandHouse`.`user_phone` AS `userPhone`, `secondHandHouse`.`created_at` AS `createdAt`, `secondHandHouse`.`updated_at` AS `updatedAt`, `estate`.`estate_name` AS `estate.estateName`, `estate`.`location` AS `estate.location`, `estate`.`city` AS `estate.city` FROM `secondHandHouse` AS `secondHandHouse` INNER JOIN `estate` AS `estate` ON `secondHandHouse`.`estate_id` = `estate`.`id`";
    let sql_count = "SELECT count(*) FROM `secondHandHouse` AS `secondHandHouse` INNER JOIN `estate` AS `estate` ON `secondHandHouse`.`estate_id` = `estate`.`id`"
    // where的SQL语句
    let where_sql = '';
    // 城市查询
    if (req.query.city) {
      sql += " AND `estate`.`city` = '广州'";
      sql_count += " AND `estate`.`city` = '广州'";
    }
    // 状态查询
    if (req.query.state) {
      sql += " AND `secondHandHouse`.`state` = '" + req.query.state + "'";
      sql_count += " AND `secondHandHouse`.`state` = '" + req.query.state + "'";
    }
    // 标题查询
    if (req.query.title) {
      sql += " AND `secondHandHouse`.`title` LIKE '%" + req.query.title + "%'"
      sql_count += " AND `secondHandHouse`.`title` LIKE '%" + req.query.title + "%'"
    }
    // 朝向查询
    if (req.query.orientation) {
      let str = '';
      for (let k of req.query.orientation) {
        str += "'" + k + "',";
      }
      str = str.substring(0, str.length - 1)
      sql += " AND `secondHandHouse`.`orientation` IN (" + str + ")";
      sql_count += " AND `secondHandHouse`.`orientation` IN (" + str + ")";
    }
    // 区级查询
    if (req.query.firstRegion) {
      // 地方查询
      if (req.query.secondRegion) {
        sql += " AND `estate`.`location` = '" + req.query.firstRegion + '-' + req.query.secondRegion + "'";
        sql_count += " AND `estate`.`location` = '" + req.query.firstRegion + '-' + req.query.secondRegion + "'";
      } else {
        sql += " AND `estate`.`location` LIKE '" + req.query.firstRegion + "%'";
        sql_count += " AND `estate`.`location` LIKE '" + req.query.firstRegion + "%'";
      }
    }
    // 价格查询 sup: [{t: '以下', v: '150'}, {t:'范围', v: ['150', '200']}, {t: '以上', v: '600'}]
    if (req.query.sup) {
      let sql1 = '';
      for (let k of req.query.sup) {
        let data = JSON.parse(k)
        if (data.t == '以下') {
          if (sql1) {
            sql1 += " OR `secondHandHouse`.`sup` <= '" + data.v + "'";
          } else {
            sql1 += " <= '" + data.v + "'";
          }
        }
        if (data.t == '以上') {
          if (sql1) {
            sql1 += " OR `secondHandHouse`.`sup` >= '" + data.v + "'";
          } else {
            sql1 += " >= '" + data.v + "'";
          }
        }
        if (data.t == '范围') {
          if (sql1) {
            sql1 += " OR `secondHandHouse`.`sup` BETWEEN '" + data.v[0] + "' AND '" + data.v[1] + "'";
          } else {
            sql1 += " BETWEEN '" + data.v[0] + "' AND '" + data.v[1] + "'";
          }
        }
      }
      // 判断是否有WHERE语句
      if (where_sql) {
        where_sql += ' AND (`secondHandHouse`.`sup`' + sql1 + ')';
      } else {
        where_sql += ' WHERE((`secondHandHouse`.`sup`' + sql1 + ')';
      }

    }
    // 面积查询 area: [{t: '以下', v: '30'}, {t:'范围', v: ['150', '200']}, {t: '以上', v: '600'}]
    if (req.query.area) {
      let sql1 = '';
      for (let k of req.query.area) {
        let data = JSON.parse(k)
        if (data.t == '以下') {
          if (sql1) {
            sql1 += " OR `secondHandHouse`.`built_area` <= '" + data.v + "'";
          } else {
            sql1 += " <= '" + data.v + "'";
          }
        }
        if (data.t == '以上') {
          if (sql1) {
            sql1 += " OR `secondHandHouse`.`built_area` >= '" + data.v + "'";
          } else {
            sql1 += " >= '" + data.v + "'";
          }
        }
        if (data.t == '范围') {
          if (sql1) {
            sql1 += " OR `secondHandHouse`.`built_area` BETWEEN '" + data.v[0] + "' AND '" + data.v[1] + "'";
          } else {
            sql1 += " BETWEEN '" + data.v[0] + "' AND '" + data.v[1] + "'";
          }
        }
      }
      // console.log('sql1==>', sql1)
      if (where_sql) {// 判断是否有WHERE语句
        where_sql += ' AND (`secondHandHouse`.`built_area`' + sql1 + ')';
      } else {
        where_sql += ' WHERE((`secondHandHouse`.`built_area`' + sql1 + ')';
      }
    }
    // 户型查询
    if (req.query.kind) {
      let sql1 = '';
      let k = req.query.kind.slice(-1);
      let data = JSON.parse(k)
      if (typeof (data) != 'number') {
        if (req.query.kind.length > 1) {
          req.query.kind.pop(req.query.kind.length);
          sql1 = " >= " + data.v + " OR `secondHandHouse`.`kind1` IN (" + req.query.kind + ")";
        } else {
          sql1 = " >= " + data.v + "";
        }
      } else {
        sql1 = " IN (" + req.query.kind + ")";
      }

      if (where_sql) {// 判断是否有WHERE语句
        where_sql += ' AND (`secondHandHouse`.`kind1`' + sql1 + ')';
      } else {
        where_sql += ' WHERE((`secondHandHouse`.`kind1`' + sql1 + ')';
      }
    }
    // 用途查询
    if (req.query.purpose) {
      let sql1 = '';
      for (let k of req.query.purpose) {
        if (sql1) {
          sql1 += " OR `secondHandHouse`.`purpose` LIKE '%" + k + "%'";
        } else {
          sql1 += " LIKE '%" + k + "%'";
        }
      }

      if (where_sql) {// 判断是否有WHERE语句
        where_sql += ' AND (`secondHandHouse`.`purpose`' + sql1 + ')';
      } else {
        where_sql += ' WHERE((`secondHandHouse`.`purpose`' + sql1 + ')';
      }
    }
    // 楼层查询
    if (req.query.floor) {
      let sql1 = '';
      for (let k of req.query.floor) {
        if (sql1) {
          sql1 += " OR `secondHandHouse`.`floor` LIKE '" + k + "%'";
        } else {
          sql1 += " LIKE '" + k + "%'";
        }
      }

      if (where_sql) {// 判断是否有WHERE语句
        where_sql += ' AND (`secondHandHouse`.`floor`' + sql1 + ')';
      } else {
        where_sql += ' WHERE((`secondHandHouse`.`floor`' + sql1 + ')';
      }
    }
    // 楼龄查询 [2, 10, {t: '以上', v: '600'}]
    if (req.query.builtYear) {
      let sql1 = '';
      for (let k of req.query.builtYear) {
        let data = JSON.parse(k)
        if (data.t == '以上') {
          if (sql1) {
            sql1 += " OR `secondHandHouse`.`built_year` <= '" + data.v + "'";
          } else {
            sql1 += " <= '" + data.v + "'";
          }
        } else {
          if (sql1) {
            sql1 += " OR `secondHandHouse`.`built_year` BETWEEN '" + data + "' AND '" + 2021 + "'";
          } else {
            sql1 += " BETWEEN '" + data + "' AND '" + 2021 + "'";
          }
        }
      }
      // console.log('sql1==>', sql1)
      if (where_sql) {// 判断是否有WHERE语句
        where_sql += ' AND (`secondHandHouse`.`built_year`' + sql1 + ')';
      } else {
        where_sql += ' WHERE((`secondHandHouse`.`built_year`' + sql1 + ')';
      }
    }
    // 装修查询
    if (req.query.decoration) {
      // params1.decoration = req.query.decoration;
      let str = '';
      for (let k of req.query.decoration) {
        str += "'" + k + "',";
      }
      str = str.substring(0, str.length - 1)
      sql += " AND `secondHandHouse`.`decoration` IN (" + str + ")";
      sql_count += " AND `secondHandHouse`.`decoration` IN (" + str + ")";
      // sql += " AND `secondHandHouse`.`decoration` IN ("+ req.query.decoration.toString() +")"
      //sql_count += " AND `secondHandHouse`.`decoration` IN ("+ req.query.decoration.toString() +")"
    }
    // 标签查询
    if (req.query.tag) {
      let str = '';
      for (let k of req.query.tag) {
        let data = JSON.parse(k)
        if (data.t == 'tag3') {
          sql += " AND `secondHandHouse`.`tag3` = '1'"
          sql_count += " AND `secondHandHouse`.`tag3` = '1'"
        } else if (data.t == 'tag4') {
          sql += " AND `secondHandHouse`.`tag4` = '1'"
          sql_count += " AND `secondHandHouse`.`tag4` = '1'"
        } else if (data.t == 'tag5') {
          sql += " AND `secondHandHouse`.`tag5` = '1'"
          sql_count += " AND `secondHandHouse`.`tag5` = '1'"
        } else if (data.t == 'tag2') {
          str += "'" + data.v + "',";
        }
      }
      if (str) {
        console.log('str==>', str)
        str = str.substring(0, str.length - 1)
        sql += " AND `secondHandHouse`.`tag2` IN (" + str + ")";
        sql_count += " AND `secondHandHouse`.`tag2` IN (" + str + ")";
      }
    }
    // 更新时间查询
    if (req.query.updatedAt) {
      sql += " AND (`secondHandHouse`.`updated_at` >= '" + req.query.updatedAt + " 00:00:00' AND `secondHandHouse`.`updated_at` <= '" + req.query.updatedAt + " 23:59:59')"
      sql_count += " AND (`secondHandHouse`.`updated_at` >= '" + req.query.updatedAt + " 00:00:00' AND `secondHandHouse`.`updated_at` <= '" + req.query.updatedAt + " 23:59:59')"
    }
    // 完善SQL语句
    if (where_sql) {
      where_sql += ')';
      sql += where_sql;
      sql_count += where_sql;
    }
    //排序并且分页
    if (req.query.order) {
      let k = JSON.parse(req.query.order)
      console.log(k)
      // 默认排序
      if (k.active == 0) {
        sql += ' LIMIT :offset, :limit';
      }
      // 最新排序
      if (k.active == 1) {
        sql += ' ORDER BY `secondHandHouse`.`updated_at` DESC LIMIT :offset, :limit';
      }
      // 总价排序
      if (k.active == 2) {
        if (k.type == 0) {
          sql += ' ORDER BY `secondHandHouse`.`sup` LIMIT :offset, :limit';
        } else {
          sql += ' ORDER BY `secondHandHouse`.`sup` DESC LIMIT :offset, :limit';
        }
      }
      // 单价排序
      if (k.active == 3) {
        if (k.type == 0) {
          sql += ' ORDER BY `secondHandHouse`.`sub` LIMIT :offset, :limit';
        } else {
          sql += ' ORDER BY `secondHandHouse`.`sub` DESC LIMIT :offset, :limit';
        }
      }
      // 面积排序
      if (k.active == 4) {
        if (k.type == 0) {
          sql += ' ORDER BY `secondHandHouse`.`built_area` LIMIT :offset, :limit';
        } else {
          sql += ' ORDER BY `secondHandHouse`.`built_area` DESC LIMIT :offset, :limit';
        }
      }
    } else {
      sql += ' LIMIT :offset, :limit';
    }
    // 原始查询
    let count = 0;
    api.query(sql_count).then(result1 => {

      count = result1[0]['count(*)'];
      api.query(sql, params).then(result => {
        res.send({
          msg: '查询成功', code: 200, data: result, total: count
        });

      }).catch(err => {
        console.log('err ==> ', err);
        res.send({ msg: '查询失败', code: 400 });
      })
    }).catch(err => {
      console.log('err ==> ', err);
      res.send({ msg: '查询失败', code: 400 });
    })
  }
  // 二手房详情
  getHousedetail(req, res) {
    console.log('----------------------二手房详情---------------------------')
    console.log('query==>', req.query)

    api.findOne({
      modelName: 'SecondHandHouse',
      where: {
        id: req.query.id
      },
      include: {
        modelName: 'Estate',
        // attributes: ['estateName', 'location', 'city']
      }
    }).then(result => {
      // console.log('result==>', result)
      res.send({ code: 200, msg: '查询成功', data: result })
    }).catch(err => {
      console.log('err==>', err)
      res.send({ code: 400, msg: '查询失败' })
    })
  }
  // 同小区房源
  getSameEstate(req, res) {
    console.log('----------------------同小区房源---------------------------')
    // console.log('query==>', req.query)

    api.findData({
      modelName: 'SecondHandHouse',
      where: { estateId: req.query.estateId },
      limit: 4,
      offset: 0,
      // attributes: ['title', 'sup', 'id', 'builtArea', 'purpose', 'img']
    }
    ).then(result => {
      console.log('result==>', result)
      if (result) {
        res.send({ code: 200, msg: '查询成功', data: result })
      } else {
        res.send({ code: 400, msg: '查询失败' })
      }
    }).catch(err => {
      console.log('err==>', err)
      res.send({ code: 400, msg: '查询失败' })
    })
  }
  // 获取经纪人列表
  getAgent(req, res) {
    console.log('----------------------获取经纪人列表---------------------------')
    console.log('query==>', req.query)

    let params = {}
    if (req.query.agentName) {
      params.agentName = {
        [Op.substring]: req.query.agentName,
      }
    }
    if (req.query.location) {
      params.location = {
        [Op.substring]: req.query.location,
      }
    }
    if (req.query.serveArea) {
      params.serveArea = {
        [Op.substring]: req.query.serveArea,
      }
    }
    if (req.query.rec) {
      params.rec = req.query.rec;
    }
    // if (req.query.updatedAt) {
    // params.updatedAt = {
    //   [Op.lte]: '2021-05-01',
    // };
    // }

    api.findCount2({
      modelName: 'Agent',
      where: params,
      limit: Number(req.query.size),
      offset: Number(req.query.page) - 1,
    }).then(result => {
      // console.log('result==>', result)
      if (result) {
        res.send({ code: 200, msg: '查询成功', data: result.rows, total: result.count })
      } else {
        res.send({ code: 400, msg: '查询失败' })
      }
    }).catch(err => {
      console.log('err==>', err)
      res.send({ code: 400, msg: '查询失败' })
    })
  }
  // 获取经纪人详情
  getAgentdetail(req, res) {
    console.log('----------------------获取经纪人详情---------------------------')
    console.log('query==>', req.query)

    api.findOne2({
      modelName: 'Agent',
      where: { id: req.query.id }
    }).then(result => {
      console.log('result==>', result)
      if (result) {
        res.send({ code: 200, msg: '查询成功', data: result })
      } else {
        res.send({ code: 400, msg: '查询失败' })
      }
    }).catch(err => {
      console.log('err==>', err)
      res.send({ code: 400, msg: '查询失败' })
    })
  }
  // 获取小区列表
  getEstateList(req, res) {
    console.log('----------------------获取小区列表---------------------------')
    console.log('query==>', req.query)

    let params = {};
    // 价格
    if (req.query.average) {
      let data = JSON.parse(req.query.average)
      console.log('data==>', data)
      if (data.type == 0) {
        params.average = {
          [Op.lte]: data.value
        }
      } else if (data.type == 1) {
        params.average = {
          [Op.between]: data.value
        }
      } else if (data.type == 2) {
        params.average = {
          [Op.gte]: data.value
        }
      }
    }
    // 小区名
    if (req.query.text) {
      params.estateName = {
        [Op.substring]: req.query.text
      }
    }
    // 城市
    if (req.query.city) {
      params.city = req.query.city
    }
    // 区级查询
    if (req.query.firstRegion) {
      // 地方查询
      if (req.query.secondRegion) {
        params.location = {
          [Op.substring]: req.query.firstRegion + '-' + req.query.secondRegion
        };
        // sql += " AND `estate`.`location` = '" + req.query.firstRegion + '-' + req.query.secondRegion + "'";
        // sql_count += " AND `estate`.`location` = '" + req.query.firstRegion + '-' + req.query.secondRegion + "'";
      } else {
        params.location = {
          [Op.substring]: req.query.firstRegion,
        };
        // sql += " AND `estate`.`location` LIKE '" + req.query.firstRegion + "%'";
        // sql_count += " AND `estate`.`location` LIKE '" + req.query.firstRegion + "%'";
      }
    }
    // 楼龄
    if (req.query.builtYear) {
      if (req.query.builtYear.charAt(0) == '<') {
        let date = new Date();
        let k = date.getFullYear() - parseInt(req.query.builtYear.substring(1));
        params.builtYear = {
          [Op.lte]: k
        }
      } else {
        let date = new Date();
        let k = date.getFullYear() - parseInt(req.query.builtYear.substring(1));
        params.builtYear = {
          [Op.gte]: k
        }
      }
    }
    // 用途
    if (req.query.purpose) {
      params.purpose = {
        [Op.substring]: req.query.purpose
      }
    }
    // 排序
    let order;
    if (req.query.order) {
      if (req.query.order == 0) {
        order = sequelize.literal('average');
      } else if (req.query.order == 1) {
        order = sequelize.literal('average DESC');
      }
    }

    api.findCount2({
      modelName: 'Estate',
      offset: Number(req.query.page) - 1,
      order: order || null,
      limit: Number(req.query.size),
      where: params
    }).then(result => {
      // console.log('result==>', result)
      if (result) {
        res.send({ code: 200, msg: '查询成功', data: result.rows, total: result.count })
      } else {
        res.send({ code: 400, msg: '查询失败' })
      }
    }).catch(err => {
      console.log('err==>', err)
      res.send({ code: 400, msg: '查询失败' })
    })
  }
  // 获取小区详情
  getEatesdetail(req, res) {
    console.log('----------------------获取小区详情---------------------------')
    console.log('query==>', req.query)

    api.findOne2({
      modelName: 'Estate',
      where: {
        id: req.query.id
      },
    }).then(result => {
      // console.log('result==>', result)
      res.send({ code: 200, msg: '查询成功', data: result })
    }).catch(err => {
      console.log('err==>', err)
      res.send({ code: 400, msg: '查询失败' })
    })
  }
  // 编辑小区详情
  editEstate(req, res) {
    console.log('----------------------编辑小区详情---------------------------')
    console.log('body==>', req.body)

    api.transaction(async (t) => {
      await api.updateData(
        'Estate',
        req.body,
        { id: req.body.id },
        t
      );
    }).then(result => {
      // console.log('result==>', result)
      res.send({ msg: '修改成功', code: 200 });
    }).catch(err => {
      console.log('err ==> ', err);
      res.send({ msg: '修改失败', code: 400 });
    })
  }
  // 获取用户列表
  getUserlist(req, res) {
    console.log('----------------------获取用户列表---------------------------')
    console.log('query==>', req.query)
    let params = {};
    if (req.query.text) {
      params = {
        [Op.or]: [
          {
            userName: {
              [Op.substring]: req.query.text
            }
          },
          {
            phone: {
              [Op.substring]: req.query.text
            }
          }]
      }
    }
    api.findCount2({
      modelName: 'User',
      where: params,
      offset: Number(req.query.page) - 1,
      limit: Number(req.query.size),
    }).then(result => {
      console.log('result==>', result)
      if (result) {
        let url = config.serverOptions.host;
        if (config.serverOptions.port) {
          url += `:${config.serverOptions.port}`
        }

        url += config.serverOptions.baseUrl
        res.send({ code: 200, msg: '查询成功', data: result.rows, total: result.count, url: url })
      } else {
        res.send({ code: 400, msg: '查询失败' })
      }
    }).catch(err => {
      console.log('err==>', err)
      res.send({ code: 400, msg: '查询失败' })
    })
  }
  // 编辑经纪人信息
  editAgent(req, res) {
    console.log('----------------------编辑经纪人信息---------------------------')
    console.log('body==>', req.body)

    api.transaction(async (t) => {
      await api.updateData(
        'Agent',
        req.body,
        { id: req.body.id },
        t
      );
    }).then(result => {
      // console.log('result==>', result)
      res.send({ msg: '修改成功', code: 200 });
    }).catch(err => {
      console.log('err ==> ', err);
      res.send({ msg: '修改失败', code: 400 });
    })
  }
  // 删除经纪人
  deleteAgent(req, res) {
    console.log('----------------------删除经纪人---------------------------')
    console.log('body==>', req.body)

    api.transaction(async (t) => {
      await api.removeData(
        'Agent',
        { id: req.body.id },
        t
      );
    }).then(result => {
      // console.log('result==>', result)
      res.send({ msg: '删除成功', code: 200 });
    }).catch(err => {
      console.log('err ==> ', err);
      res.send({ msg: '删除失败', code: 400 });
    })
  }
  // 删除小区
  deleteEstate(req, res) {
    console.log('----------------------删除小区---------------------------')
    console.log('body==>', req.body)

    api.transaction(async (t) => {
      await api.removeData(
        'Estate',
        { id: req.body.id },
        t
      );
    }).then(result => {
      // console.log('result==>', result)
      res.send({ msg: '删除成功', code: 200 });
    }).catch(err => {
      console.log('err ==> ', err);
      res.send({ msg: '删除失败', code: 400 });
    })
  }
  // 删除用户
  deleteUser(req, res) {
    console.log('----------------------删除用户---------------------------')
    console.log('body==>', req.body)

    api.transaction(async (t) => {
      await api.removeData(
        'User',
        { id: req.body.id },
        t
      );
    }).then(result => {
      // console.log('result==>', result)
      res.send({ msg: '删除成功', code: 200 });
    }).catch(err => {
      console.log('err ==> ', err);
      res.send({ msg: '删除失败', code: 400 });
    })
  }
  // 编辑用户
  editUser(req, res) {
    console.log('----------------------编辑用户---------------------------')
    console.log('body==>', req.body)

    api.transaction(async (t) => {
      await api.updateData(
        'User',
        req.body,
        { id: req.body.id },
        t
      );
    }).then(result => {
      // console.log('result==>', result)
      res.send({ msg: '修改成功', code: 200 });
    }).catch(err => {
      console.log('err ==> ', err);
      res.send({ msg: '修改失败', code: 400 });
    })
  }
  // 获取管理员列表
  getAdminList(req, res) {
    console.log('----------------------获取管理员列表---------------------------')
    console.log('query==>', req.query)
    let params = {};
    if (req.query.text) {
      params = {
        [Op.or]: [
          {
            adminName: {
              [Op.substring]: req.query.text
            }
          },
          {
            email: {
              [Op.substring]: req.query.text
            }
          }]
      }
    }
    api.findCount2({
      modelName: 'Admin',
      where: params,
      offset: Number(req.query.page) - 1,
      limit: Number(req.query.size),
    }).then(result => {
      console.log('result==>', result)
      if (result) {
        let url = config.serverOptions.host;
        if (config.serverOptions.port) {
          url += `:${config.serverOptions.port}`
        }

        url += config.serverOptions.baseUrl
        res.send({ code: 200, msg: '查询成功', data: result.rows, total: result.count, url: url })
      } else {
        res.send({ code: 400, msg: '查询失败' })
      }
    }).catch(err => {
      console.log('err==>', err)
      res.send({ code: 400, msg: '查询失败' })
    })
  }
  // 编辑管理员
  editAdmin(req, res) {
    console.log('----------------------编辑管理员---------------------------')
    console.log('body==>', req.body)

    api.transaction(async (t) => {
      await api.updateData(
        'Admin',
        req.body,
        { id: req.body.id },
        t
      );
    }).then(result => {
      // console.log('result==>', result)
      res.send({ msg: '修改成功', code: 200 });
    }).catch(err => {
      console.log('err ==> ', err);
      res.send({ msg: '修改失败', code: 400 });
    })
  }
  // 删除管理员
  deleteAdmin(req, res) {
    console.log('----------------------删除管理员---------------------------')
    console.log('body==>', req.body)

    api.transaction(async (t) => {
      await api.removeData(
        'Admin',
        { id: req.body.id },
        t
      );
    }).then(result => {
      // console.log('result==>', result)
      res.send({ msg: '删除成功', code: 200 });
    }).catch(err => {
      console.log('err ==> ', err);
      res.send({ msg: '删除失败', code: 400 });
    })
  }

}

//导出实例
module.exports = new RoutesController();